package com.wxd.service.impl;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.wxd.dao.AccessDB;
import com.wxd.dao.ConnectionManager;
import com.wxd.service.DevService;

public class DevServiceImpl implements DevService {

	public int getRecordCount() {
		int count = 0;
		String countSql = "select count(1) from 运行信息";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getInstance().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(countSql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		} finally {
			AccessDB.close(conn, stmt, rs);
		}
		return count;
	}

	public JSONArray getDevRunInfo(int start, int limit, String devSpi,
			String ownUnit, String startTime, String endTime) {

		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(startTime)) {
			whereStr.append(" and 开始时间>='" + startTime + "'");
		}
		if (!StringUtils.isEmpty(endTime)) {
			whereStr.append(" and 停止时间<='" + endTime + "'");
		}

		JSONArray result = new JSONArray();
		String sql = "select top " + limit
				+ " * from 运行信息 where id <= (select min(id) from (select top "
				+ (start + 1) + " id from 运行信息 where 1=1 "
				+ whereStr.toString() + " order by id desc) as t) "
				+ whereStr.toString() + " order by id desc";

		List queryForSql = AccessDB.queryForSql(sql,false);

		for (int i = 0; i < queryForSql.size(); i++) {
			Map map = (Map) queryForSql.get(i);

			JSONObject object = new JSONObject();

			object.put("id", map.get(1));
			object.put("devCode", map.get(2));
			object.put("devName", map.get(3));
			object.put("devSpi", map.get(4));
			object.put("ownUnit", map.get(5));
			object.put("tn", map.get(6));
			object.put("startTime", map.get(7).toString());
			object.put("endTime", map.get(8).toString());

			String runTime = map.get(9).toString();
			runTime = runTime.replaceAll("\\r", "");
			runTime = runTime.replaceAll("\\n", "");

			int runTimeNum = 0;
			try {
				runTimeNum = Integer.parseInt(runTime);
			} catch (Exception e) {
			}

			object.put("runTime", runTimeNum);
			object.put("runDistance", map.get(10)==null?"":map.get(10).toString());
			object.put("weidu", map.get(11)==null?"":map.get(11).toString());
			object.put("jingdu", map.get(12)==null?"":map.get(12).toString());
			result.add(object);
		}

		return result;
	}

	public JSONArray getDevSpiList() {
		String sql = "select 设备规格 from 运行信息 group by 设备规格";
		List queryList = AccessDB.queryForSql(sql,false);

		JSONArray result = new JSONArray();

		for (int i = 0; i < queryList.size(); i++) {
			Map map = (Map) queryList.get(i);

			JSONObject jo = new JSONObject();
			jo.put("devSpiCode", map.get(1));
			jo.put("devSpiName", map.get(1));
			jo.put("text", map.get(1));
			jo.put("id", map.get(1));
			jo.put("leaf", true);

			result.add(jo);
		}
		return result;
	}

	public JSONArray getOwnUnitList() {
		String sql = "select 归属单位 from 运行信息 group by 归属单位";
		List queryList = AccessDB.queryForSql(sql,false);

		JSONArray result = new JSONArray();

		for (int i = 0; i < queryList.size(); i++) {
			Map map = (Map) queryList.get(i);

			JSONObject jo = new JSONObject();
			jo.put("ownUnitCode", map.get(1));
			jo.put("ownUnitName", map.get(1));
			jo.put("text", map.get(1));
			jo.put("id", map.get(1));
			jo.put("leaf", true);

			result.add(jo);
		}
		return result;
	}

	public JSONArray getOperatorList() {
		String sql = "select 操作员 from 设备信息 group by 操作员";
		List queryList = AccessDB.queryForSql(sql,false);

		JSONArray result = new JSONArray();

		for (int i = 0; i < queryList.size(); i++) {
			Map map = (Map) queryList.get(i);

			JSONObject jo = new JSONObject();
			jo.put("operatorCode", map.get(1));
			jo.put("operatorName", map.get(1));
			result.add(jo);
		}
		return result;
	}

	public JSONArray getDevList() {
		String sql = "select 设备编号,设备名称 from 设备信息 group by 设备编号,设备名称";
		List queryList = AccessDB.queryForSql(sql,false);

		JSONArray result = new JSONArray();

		for (int i = 0; i < queryList.size(); i++) {
			Map map = (Map) queryList.get(i);

			JSONObject jo = new JSONObject();
			jo.put("devCode", map.get(1));
			jo.put("devName", map.get(2));
			result.add(jo);
		}
		return result;
	}

	public HSSFWorkbook getExcelInfo(String devSpi, String ownUnit,
			String startTime, String endTime) {
		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(startTime)) {
			whereStr.append(" and 开始时间>='" + startTime + "'");
		}
		if (!StringUtils.isEmpty(endTime)) {
			whereStr.append(" and 停止时间<='" + endTime + "'");
		}
		String sql = "select * from 运行信息 where 1=1 " + whereStr
				+ " order by id desc";

		List data = AccessDB.queryForSql(sql,false);
		for (int i = 0; i < data.size(); i++) {
			Map map = (Map) data.get(i);
			JSONObject object = new JSONObject();
			String runTime = map.get(9).toString();
			runTime = runTime.replaceAll("\\r", "");
			runTime = runTime.replaceAll("\\n", "");
			int runTimeNum = 0;
			try {
				runTimeNum = Integer.parseInt(runTime);
			} catch (Exception e) {
			}

			map.put(9, runTimeNum);
		}

		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet();

		HSSFRow row = sheet.createRow(0);
		row.setHeight((short) 500);

		/*
		 * 设置excel的列头信息 分别为 纳税人识别码 纳税人名称 入库级次 数量 一般预算收入(元) 税收收入(元) 基金规费(元)
		 */
		List<String> cellTitleList = new ArrayList<String>();
		cellTitleList.add("ID");
		cellTitleList.add("设备编号");
		cellTitleList.add("设备名称");
		cellTitleList.add("设备规格");
		cellTitleList.add("归属单位");
		cellTitleList.add("通信号码");
		cellTitleList.add("开始时间");
		cellTitleList.add("停止时间");
		cellTitleList.add("运行时间");
		cellTitleList.add("运行里程");
		cellTitleList.add("当前维度");
		cellTitleList.add("当前经度");

		// 编辑列头样式
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFillForegroundColor((short) 44);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font = workbook.createFont();
		font.setFontName("黑体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		createCellTitle(row, cellTitleList, style);

		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow(i + 1);
			Map rowData = (Map) data.get(i);

			for (int n = 0; n < rowData.size(); n++) {
				HSSFCell cell = row.createCell(n);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(rowData.get(n + 1)==null?"":rowData.get(n + 1).toString());
			}
		}

		// 放入汇总数据aaaaaaaaaa
		row = sheet.createRow(data.size() + 1);
		Map statistics = statistics(whereStr.toString());
		for (int i = 0; i < 4; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			if (i == 0) {
				cell.setCellValue("总设备数:" + statistics.get(i + 1).toString());
			} else if (i == 1) {
				cell.setCellValue("总记录数:"
						+ ((int) Double.parseDouble(statistics.get(i + 1)
								.toString())));
			} else if(i==2){
				cell.setCellValue("总运行时间:"
						+ ((int) Double.parseDouble(statistics.get(i + 1)
								.toString())));
			}else{
				cell.setCellValue("总运行里程:"
						+ ((int) Double.parseDouble(statistics.get(i + 1)
								.toString())));
			}
		}

		sheet.setColumnWidth(1, 6000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 6000);
		sheet.setColumnWidth(5, 6000);
		sheet.setColumnWidth(6, 10000);
		sheet.setColumnWidth(7, 10000);
		sheet.createFreezePane(0, 1, 0, 1);
		return workbook;
	}

	// 创建excel的列头信息
	private void createCellTitle(HSSFRow row, List<String> list,
			HSSFCellStyle style) {
		for (int i = 0; i < list.size(); i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(list.get(i));
			cell.setCellStyle(style);
		}
	}

	public Map statistics(String devSpi, String ownUnit, String startTime,
			String endTime) {
		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(startTime)) {
			whereStr.append(" and 开始时间>='" + startTime + "'");
		}
		if (!StringUtils.isEmpty(endTime)) {
			whereStr.append(" and 停止时间<='" + endTime + "'");
		}
		return statistics(whereStr.toString());
	}

	private Map statistics(String whereStr) {
		String sql = "select count(num),sum(num),sum(runTime),sum(runDistance) from (select count(设备编号) as num,sum(convert(float,运行时间)) as runTime,sum(convert(float,运行里程)) as runDistance from 运行信息 where 1=1 "
				+ whereStr + " group by 设备编号) as t";

		List list = AccessDB.queryForSql(sql,false);

		if (list.size() > 0) {
			return (Map) list.get(0);
		} else {

			return null;
		}
	}

	public StringBuffer devCount(String type) {
		StringBuffer result =new StringBuffer();
		String sql = "";
		if ("1".equals(type)) {
			sql = "select 归属单位,count(*) from 设备信息 group by 归属单位";
			result.append("<graph showNames='1' caption='归属单位设备数据总览' baseFontSize='12' decimalPrecision='0'>");
		} else {
			sql = "select 设备规格,count(*) from 设备信息 group by 设备规格";
			result.append("<graph showNames='1' caption='设备规格设备数据总览' baseFontSize='12' decimalPrecision='0'>");
		}

		List queryForSql = AccessDB.queryForSql(sql,false);
		

		for (int i = 0; i < queryForSql.size(); i++) {
			Map map = (Map) queryForSql.get(i);
			result.append("<set name='"+map.get(1)+"' value='"+map.get(2)+"' isSliced='1'/>");
		}
		
		result.append("</graph>");
		return result;
	}

	public JSONArray geDevBaseInfo(int start, int limit, String devSpi,
			String ownUnit, String operator) {
		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(operator)) {
			whereStr.append(" and 操作员='" + operator + "'");
		}

		String sql = "select top " + limit
				+ " * from 设备信息 where id >= (select max(id) from (select top "
				+ (start + 1) + " id from 设备信息 where 1=1 " + whereStr
				+ " order by id asc) as t) " + whereStr + " order by id asc";

		List list = AccessDB.queryForSql(sql,false);

		JSONArray result = new JSONArray();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		for (int i = 0; i < list.size(); i++) {
			Map map = (Map) list.get(i);

			JSONObject obj = new JSONObject();
			obj.put("id", map.get(1));
			obj.put("devCode", map.get(2));
			obj.put("devName", map.get(3));
			obj.put("devSpi", map.get(4));
			obj.put("tn", map.get(5));
			obj.put("ownUnit", map.get(6));
			obj.put("comDate", format.format(map.get(7)));
			obj.put("operator", map.get(8));

			result.add(obj);
		}

		return result;
	}

	public int getDevBaseRecordCount(String devSpi, String ownUnit,
			String operator) {
		StringBuffer whereStr = new StringBuffer();

		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(operator)) {
			whereStr.append(" and 操作员='" + operator + "'");
		}

		String sql = "select count(*) from 设备信息 where 1=1"
				+ whereStr.toString();

		List queryForSql = AccessDB.queryForSql(sql,false);

		if (queryForSql.size() > 0) {
			Map map = (Map) queryForSql.get(0);
			try {
				return Integer.parseInt(map.get(1).toString());
			} catch (Exception e) {
			}
		}
		return 0;
	}

	public HSSFWorkbook getDevBaseExcelInfo(String devSpi, String ownUnit,
			String operator) {
		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(operator)) {
			whereStr.append(" and 操作员='" + operator + "'");
		}

		String sql = "select * from 设备信息 where 1=1 " + whereStr
				+ " order by id asc";

		List data = AccessDB.queryForSql(sql,false);

		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet();

		HSSFRow row = sheet.createRow(0);
		row.setHeight((short) 500);

		/*
		 * 设置excel的列头信息 分别为 纳税人识别码 纳税人名称 入库级次 数量 一般预算收入(元) 税收收入(元) 基金规费(元)
		 */
		List<String> cellTitleList = new ArrayList<String>();
		cellTitleList.add("ID");
		cellTitleList.add("设备编号");
		cellTitleList.add("设备名称");
		cellTitleList.add("设备规格");
		cellTitleList.add("通信号码");
		cellTitleList.add("归属单位");
		cellTitleList.add("投产日期");
		cellTitleList.add("操作员");

		// 编辑列头样式
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFillForegroundColor((short) 44);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font = workbook.createFont();
		font.setFontName("黑体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		createCellTitle(row, cellTitleList, style);
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow(i + 1);
			Map rowData = (Map) data.get(i);

			for (int n = 0; n < rowData.size(); n++) {
				HSSFCell cell = row.createCell(n);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				if (n == 6) {
					cell.setCellValue(format.format(rowData.get(n + 1)));
				} else {
					cell.setCellValue(rowData.get(n + 1).toString());
				}
			}
		}

		sheet.setColumnWidth(1, 6000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 6000);
		sheet.setColumnWidth(5, 6000);
		sheet.setColumnWidth(6, 10000);
		sheet.setColumnWidth(7, 10000);
		sheet.createFreezePane(0, 1, 0, 1);
		return workbook;
	}

	public int getDevMaintainRecordCount(String devCode, String devSpi,
			String ownUnit, String startTime, String endTime) {
		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(devCode)) {
			whereStr.append(" and 设备编号='" + devCode + "'");
		}
		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(startTime)) {
			whereStr.append(" and 维护时间>='" + startTime + "'");
		}
		if (!StringUtils.isEmpty(endTime)) {
			whereStr.append(" and 维护时间<='" + endTime + "'");
		}

		String sql = "select count(*) from 维护信息 where 1=1"
				+ whereStr.toString();

		List queryForSql = AccessDB.queryForSql(sql,false);

		if (queryForSql.size() > 0) {
			Map map = (Map) queryForSql.get(0);
			try {
				return Integer.parseInt(map.get(1).toString());
			} catch (Exception e) {
			}
		}
		return 0;
	}

	public JSONArray getDevMaintainInfo(int start, int limit, String devCode,
			String devSpi, String ownUnit, String startTime, String endTime) {
		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(devCode)) {
			whereStr.append(" and 设备编号='" + devCode + "'");
		}
		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(startTime)) {
			whereStr.append(" and 维护时间>='" + startTime + "'");
		}
		if (!StringUtils.isEmpty(endTime)) {
			whereStr.append(" and 维护时间<='" + endTime + "'");
		}

		String sql = "select top " + limit
				+ " * from 维护信息 where id <= (select min(id) from (select top "
				+ (start + 1) + " id from 维护信息 where 1=1 " + whereStr
				+ " order by id desc) as t) " + whereStr + " order by id desc";

		List list = AccessDB.queryForSql(sql,false);

		JSONArray result = new JSONArray();
		for (int i = 0; i < list.size(); i++) {
			Map map = (Map) list.get(i);

			JSONObject obj = new JSONObject();
			obj.put("id", map.get(1));
			obj.put("devCode", map.get(2));
			obj.put("devName", map.get(3));
			obj.put("devSpi", map.get(4));
			obj.put("ownUnit", map.get(5));
			obj.put("mainPeoNum", map.get(6));
			obj.put("maintainTime", map.get(7).toString());
			obj.put("maintainContent", map.get(8));
			result.add(obj);
		}
		return result;
	}

	public HSSFWorkbook downloadDevWHInfo(String devCode, String devSpi,
			String ownUnit, String startTime, String endTime) {
		StringBuffer whereStr = new StringBuffer();
		if (!StringUtils.isEmpty(devCode)) {
			whereStr.append(" and 设备编号='" + devCode + "'");
		}
		if (!StringUtils.isEmpty(devSpi)) {
			whereStr.append(" and 设备规格='" + devSpi + "'");
		}
		if (!StringUtils.isEmpty(ownUnit)) {
			whereStr.append(" and 归属单位='" + ownUnit + "'");
		}

		if (!StringUtils.isEmpty(startTime)) {
			whereStr.append(" and 维护时间>='" + startTime + "'");
		}
		if (!StringUtils.isEmpty(endTime)) {
			whereStr.append(" and 维护时间<='" + endTime + "'");
		}

		String sql = "select * from 维护信息 where 1=1 " + whereStr
				+ " order by id desc";

		List data = AccessDB.queryForSql(sql,false);

		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet();

		HSSFRow row = sheet.createRow(0);
		row.setHeight((short) 500);

		List<String> cellTitleList = new ArrayList<String>();
		cellTitleList.add("ID");
		cellTitleList.add("设备编号");
		cellTitleList.add("设备名称");
		cellTitleList.add("设备规格");
		cellTitleList.add("归属单位");
		cellTitleList.add("养护人号码");
		cellTitleList.add("维护时间");
		cellTitleList.add("维护内容");

		// 编辑列头样式
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFillForegroundColor((short) 44);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font = workbook.createFont();
		font.setFontName("黑体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		createCellTitle(row, cellTitleList, style);

		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow(i + 1);
			Map rowData = (Map) data.get(i);

			for (int n = 0; n < rowData.size(); n++) {
				HSSFCell cell = row.createCell(n);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(rowData.get(n + 1).toString());
			}
		}

		sheet.setColumnWidth(1, 6000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 6000);
		sheet.setColumnWidth(5, 6000);
		sheet.setColumnWidth(6, 10000);
		sheet.setColumnWidth(7, 10000);
		sheet.createFreezePane(0, 1, 0, 1);
		return workbook;
	}

	public JSONArray getDevMonthRunInfo(int year) {
		JSONArray result = new JSONArray();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getInstance().getConnection();
			for(int x=1;x<=12;x++){
				if(x==12){
					String sql = "select 归属单位,sum(运行时间) from 运行信息 where 停止时间>='"+year+"-12-01 00:00:00' and 停止时间<'"+(year+1)+"-01-01 00:00:00' group by 归属单位";
					pstm = conn.prepareStatement(sql);
					rs = pstm.executeQuery();
					JSONObject monthJSON = new JSONObject();
					monthJSON.put("month", x+"月");
					while (rs.next()) {
						monthJSON.put(rs.getString(1), rs.getInt(2));
					}
					result.add(monthJSON);
				}else{
					String sql = "select 归属单位,sum(运行时间) from 运行信息 where 停止时间>='"+year+"-"+x+"-01 00:00:00' and 停止时间<'"+year+"-"+(x+1)+"-01 00:00:00' group by 归属单位";
					pstm = conn.prepareStatement(sql);
					rs = pstm.executeQuery();
					JSONObject monthJSON = new JSONObject();
					monthJSON.put("month", x+"月");
					while (rs.next()) {
						monthJSON.put(rs.getString(1), rs.getInt(2));
					}
					result.add(monthJSON);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		} finally {
			AccessDB.close(conn, pstm, rs);
		}
		return result;
	}

	public JSONArray getTopRunTimeInfo() {
		JSONArray result = new JSONArray();
		String sql = "select top 8 设备编号,设备名称,设备规格,归属单位,sum(convert(float,运行时间)) as totalRunTime from 运行信息 group by 设备编号,设备名称,设备规格,归属单位 order by totalRunTime desc";
		List queryForSql = AccessDB.queryForSql(sql,false);
		
		for(int n=0;n<queryForSql.size();n++){
			Map map = (Map)queryForSql.get(n);
			
			JSONObject obj = new JSONObject();
			obj.put("devCode", map.get(1));
			obj.put("devName", map.get(2));
			obj.put("devSpi", map.get(3));
			obj.put("ownUnit", map.get(4));
			obj.put("totalRunTime", map.get(5));
			result.add(obj);
		}
		return result;
	}
	
	public JSONArray getTopRunDistanceInfo() {
		JSONArray result = new JSONArray();
		String sql = "select top 8 设备编号,设备名称,设备规格,归属单位,sum(convert(float,运行里程)) as totalDistance from 运行信息 group by 设备编号,设备名称,设备规格,归属单位 order by totalDistance desc";
		List queryForSql = AccessDB.queryForSql(sql,false);
		
		for(int n=0;n<queryForSql.size();n++){
			Map map = (Map)queryForSql.get(n);
			
			JSONObject obj = new JSONObject();
			obj.put("devCode", map.get(1));
			obj.put("devName", map.get(2));
			obj.put("devSpi", map.get(3));
			obj.put("ownUnit", map.get(4));
			obj.put("totalRunDistance", map.get(5));
			result.add(obj);
		}
		return result;
	}
	
}
